ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks #478 - Introduction to Power Automate

Lego graph image

Author: Thomas Edmunds

Published: 05 Sep 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello and welcome back to Excel Tips and Tricks! This week we have a Developer level post where we will demonstrate how to use Power Automate to run an Office Script against multiple Excel files simultaneously.

No prior knowledge of Power Automate is required to follow the steps in this article. An introduction to Office Scripts has been covered further in Tip #470.

Please note that to follow the steps in the example you will need a work email address linked to a Microsoft 365 subscription.

Creating the office script

Before diving into Power Automate, we need an Office Script to work with.

Here we have a very simple script that will perform two actions for a given Excel file:

  1. Insert two rows at the top of the active worksheet, and
  2. Add the title “Excel Tips & Tricks Ltd” in cell “A1”.
Introduction to Power Automate
To create this script, you can open Excel and use the “Record Actions” button under the “Automate” tab.
Introduction to Power Automate
Insert two rows at the top of the spreadsheet then add the title and press “Stop recording”.

Excel will create a new script in your OneDrive folder with a default name e.g., “Script 1”.

In this example I have then changed the script name to “Insert title”.
Introduction to Power Automate

Target files

To follow the example, you can save any collection of Excel files in a folder in your OneDrive.

For demonstration purposes I have five Excel files saved in a folder on my OneDrive that all contain data in a similar format as the example above.
Introduction to Power Automate

Getting started with Power Automate

Now we have all the pieces in place ready to link them together with Power Automate!

Power Automate can be found by navigating to powerautomate.com in your web browser and signing in with your Microsoft 365 account.

If you are already logged in to any of the Microsoft 365 apps in your web browser clicking the 9 dots in the top left corner will bring up the app switcher, and from there you can find Power Automate.
Introduction to Power Automate

Setting up the flow

Flow is the term used to describe a sequence of actions that link together in a repeatable way that we specify.

Once we are in Power Automate, we can choose “Create” to start building our flow.
Introduction to Power Automate
Next choose “Instant cloud flow” to create a flow that will run on demand.
Introduction to Power Automate
Now we need to specify how the flow will start. The first option to “Manually trigger a flow” is the standard first step for a flow that will be triggered on demand.
Introduction to Power Automate
Choose “Create” and we will be taken to the flow builder page as follows:
Introduction to Power Automate
Flows in Power Automate run from top to bottom with each step represented by a horizontal bar that can be clicked on to expand or collapse as required.

Connecting to connectors!

Connectors are the links between Power Automate and any app outside of Power Automate (e.g., Excel, OneDrive, Teams, Outlook etc.).

Choose “+ New step” and we are presented with all the connectors available to us. Let’s search by name and type in “Excel”.

The option we want is “Excel Online (Business)”.
Introduction to Power Automate

Light, Camera... Action!

Actions are the things that you can do with connectors.

Here we are presented with a list of actions relevant to the “Excel Online (Business)” connector. We want to choose “Run script”.
Introduction to Power Automate

You are then presented with four boxes with drop downs.

  1. Location: click the drop-down and choose “OneDrive for Business”.
Introduction to Power Automate
  1. Document Library. This will be “OneDrive”.
Introduction to Power Automate
  1. File: Select the file against which we will run the script. Here we can use the browse icon on the right-hand side. Let’s choose the first Excel file in the folder.
Introduction to Power Automate
  1. Choose the script from the Office Scripts saved in our OneDrive.
Introduction to Power Automate
I will choose “Insert title” as that is the script created earlier in this example.

Now let’s press Save and create the flow. A green banner should appear to confirm this.
Introduction to Power Automate
Well done for creating a Power Automate flow!

Editing your Power Automate flow

Remember that we want to run this script against all files in the folder, not just the one we selected.

To change the behaviour of the script to update all Excel files in the folder we can create an intermediate step as follows.

Hover the mouse pointer over the arrow between the two steps and you will see a “+” icon appear.
Introduction to Power Automate
Click on this and choose “Add an action”.

From here we can search for the “OneDrive for business” connector and the action to “List files in folder”.
Introduction to Power Automate
In the same way as we selected the file before we can use the browse icon on the right-hand side to select our OneDrive folder containing the files we want to target.

Note: you will be presented with the “Root” parent folder for your OneDrive. Click on the greater than symbol to drill down into your OneDrive folders.
Introduction to Power Automate
After you have selected your folder, you can go back into the green “Run script” step and delete the file location in the “File” field.

In its place we need to reference the individual file IDs for each file in our OneDrive folder.

From the right-hand side, you have a separate menu to insert dynamic content.

(Dynamic content is the term used to refer to information gathered in previous steps.)

Click on “Id” to bring in a reference to all your files in the OneDrive folder.
Introduction to Power Automate
You will notice that Power Automate has created an “Apply to each” box around our “Run script” action.
Introduction to Power Automate
And that’s it! We have now updated our flow with desired behaviour. Now we can choose to save and take it for a spin. Choose “Test” in the top right corner.
Introduction to Power Automate
A task pane will appear on the right-hand side to test the flow. Click on “Manually” and continue through to the next screen.

Power Automate will check that all your connections are live and ready to run the flow. If not, you will have the option to sign in.
Introduction to Power Automate
And finally, the moment of truth, you can run the flow.

Now that it has started you can click into the “Flow Runs Page” to monitor the progress of it. And of course, you can open your Excel files on another tab in your browser to see the magic happening in the background!
Introduction to Power Automate

Power Automate and Excel (and other apps…)

The techniques shown in this example can generate huge potential time savings when dealing with more complex scripts and a larger number of files.

As well as the “Run script” action Power Automate can create flows that for example update a spreadsheet every time an email is received in Outlook.

I would highly recommend exploring the different connectors and actions within Power Automate and start experimenting with your own flows!
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.